/*
 * To change this template, choose Tools | Templates and open the template in
 * the editor.
 */
package eypweb_bsc;

import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import javafx.collections.ObservableList;
import javafx.stage.FileChooser;
import javafx.stage.Stage;
import jxl.*;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 *
 * @author Kohalmi Istvan
 */

// Excel fajlokat kezelo osztaly
public class XLSHandler {

    // egyelore nincs szukseg peldanyositasra
    public XLSHandler() {}

    // muveletek betoltese
    public static ArrayList<Operation> importOperation() {
        Director director = Director.getInstance();
        AccessInfo ai = director.getAccessInfo();
        // a betoltott objektumok listaja
        ArrayList ops = new ArrayList<Operation>();
        Stage stage = director.getStage();
        FileChooser fc = new FileChooser();
        // a parbeszedablak csak xls kiterjesztesu fajlokat mutasson
        FileChooser.ExtensionFilter filter = new FileChooser.ExtensionFilter("Excel munkafüzet (*.xls)", "*.xls");
        fc.getExtensionFilters().add(filter);
        fc.setTitle("Tranzakciók importálása...");
        fc.setInitialDirectory(File.listRoots()[0]);
        // megnyito kepernyo mutatasa
        File tran = fc.showOpenDialog(stage);
        try {
            WorkbookSettings ws = new WorkbookSettings();
            // enelkul az ekezetes karaktereket nem helyesen olvassa be
            ws.setEncoding("Cp1252");
            // munkafuzet megnyitasa
            Workbook wb = Workbook.getWorkbook(tran,ws);
            // tranzakciok lap megnyitasa
            Sheet sheet = wb.getSheet("tranzakciok");
            // sorok szama
            int rowCount= sheet.getRows();
            // 2.-tol kezdve minden sornal
            for (int i=1; i<rowCount; i++)
            {
                // elmentjuk a sorban levo ertekeket
                Cell[] cells = sheet.getRow(i);
                // betoltjuk azokat egy uj Operation objektumba
                Operation op = new Operation();
                op.setType(((LabelCell)cells[1]).getString());
                op.setPort(((LabelCell)cells[2]).getString());
                op.setIsin(Isin.getCurrentIsin(((LabelCell)cells[3]).getString()));
                op.setOperationId(((LabelCell)cells[4]).getString());
                op.setBindingDate(new Date(((DateCell)cells[5]).getDate().getTime()));
                op.setFulfillmentDate(new Date(((DateCell)cells[6]).getDate().getTime()));
                op.setExecutionDate(new Date(((DateCell)cells[7]).getDate().getTime()));
                op.setQuantity((int)((NumberCell)cells[8]).getValue());
                op.setNominal(((NumberCell)cells[9]).getValue());
                op.setNetRateValue(Math.round(((NumberCell)cells[10]).getValue()));
                op.setDividend(Math.round(((NumberCell)cells[11]).getValue()));
                op.setAccumulatedInterest(Math.round(((NumberCell)cells[12]).getValue()));
                op.setYield(Math.round(((NumberCell)cells[13]).getValue()));
                op.setGrossPrice(Math.round(((NumberCell)cells[14]).getValue()));
                op.setCommissionHUF(Math.round(((NumberCell)cells[15]).getValue()));
                op.setCapitalValue(Math.round(((NumberCell)cells[16]).getValue()));
                op.setBindingRate((float)((NumberCell)cells[17]).getValue());
                op.setFulfillmentRate((float)((NumberCell)cells[18]).getValue());
                op.setExecutionRate((float)((NumberCell)cells[19]).getValue());
                op.setCurrencyRate(((NumberCell)cells[20]).getValue());
                op.setNetRatePercent((float)((NumberCell)cells[21]).getValue());
                op.setNote(((LabelCell)cells[22]).getString());
                op.setAccumulatedInterestPercent((float)((NumberCell)cells[23]).getValue());
                op.setYieldPercent((float)((NumberCell)cells[24]).getValue());
                op.setGrossPriceCurrency(((NumberCell)cells[25]).getValue());
                op.setCommissionCurrency(((NumberCell)cells[26]).getValue());
                // lista bovitese
                ops.add(op);
            }
        } catch (Exception e) {
            System.err.println(e.getMessage());
        }
        // lista referenciajat visszaadja
        return ops;
    }

    // deviza exportalasa
    public static File exportCurrency(ObservableList<Currency> list){
        Director director = Director.getInstance();
        Stage stage = director.getStage();
        FileChooser fc = new FileChooser();
        FileChooser.ExtensionFilter filter = new FileChooser.ExtensionFilter("Excel munkafüzet (*.xls)", "*.xls");
        fc.getExtensionFilters().add(filter);
        fc.setTitle("XLS exportálása...");
        fc.setInitialDirectory(File.listRoots()[0]);
        File exportXLS = fc.showSaveDialog(stage);
        try{
            // irhato munkafuzet megnyitasa
            WritableWorkbook wb = Workbook.createWorkbook(exportXLS);
            // irha
            WritableSheet sheet = wb.createSheet("devizak", 0);
            // fejlec cellainak letrehozasa
            Label h1 = new Label(0,0,"Megnevezés");
            Label h2 = new Label(1,0,"Egység");
            Label h3 = new Label(2,0,"Hatály kezdete");
            Label h4 = new Label(3,0,"Hatály vége");
            // fejlec cellak bejegyzese
            sheet.addCell(h1);
            sheet.addCell(h2);
            sheet.addCell(h3);
            sheet.addCell(h4);
            // a megadott lista minden elemere
            for (int i=0; i< list.size(); i++)
            {
                // adatcellak letrehozasa
                Label name = new Label(0,i+1,list.get(i).getName());
                jxl.write.Number unit = new jxl.write.Number(1,i+1,Integer.parseInt(list.get(i).getUnit()));
                Label start = new Label(2,i+1,list.get(i).getStartDate());
                Label end = new Label(3,i+1,list.get(i).getEndDate());
                // adatcellak bejegyzese
                sheet.addCell(name);
                sheet.addCell(unit);
                sheet.addCell(start);
                sheet.addCell(end);
            }
            // munkafuzet irasa
            wb.write();
            // munkafuzet lezarasa
            wb.close();
        }
        catch(Exception e)
        {
            System.err.println("Írási hiba \n" + e.getMessage());
        }
        return exportXLS;
    }
    
    // eszkozcsoportok exportalasa
    public static File exportInstrumentGroup(ObservableList<InstrumentGroup> list){
        Director director = Director.getInstance();
        Stage stage = director.getStage();
        FileChooser fc = new FileChooser();
        FileChooser.ExtensionFilter filter = new FileChooser.ExtensionFilter("Excel munkafüzet (*.xls)", "*.xls");
        fc.getExtensionFilters().add(filter);
        fc.setTitle("XLS exportálása...");
        fc.setInitialDirectory(File.listRoots()[0]);
        File exportXLS = fc.showSaveDialog(stage);
        try{
            WritableWorkbook wb = Workbook.createWorkbook(exportXLS);
            WritableSheet sheet = wb.createSheet("eszkoz csoportok", 0);
            Label h1 = new Label(0,0,"Megnevezés");
            Label h2 = new Label(1,0,"Hatály kezdete");
            Label h3 = new Label(2,0,"Hatály vége");
            sheet.addCell(h1);
            sheet.addCell(h2);
            sheet.addCell(h3);
            for (int i=0; i< list.size(); i++)
            {
                Label name = new Label(0,i+1,list.get(i).getName());
                Label start = new Label(1,i+1,list.get(i).getStartDate());
                Label end = new Label(2,i+1,list.get(i).getEndDate());
                sheet.addCell(name);
                sheet.addCell(start);
                sheet.addCell(end);
            }
            wb.write();
            wb.close();
        }
        catch(Exception e)
        {
            System.err.println("Írási hiba \n" + e.getMessage());
        }
        return exportXLS;
    }
    
    // befektetesi csoportok exportalasa
    public static File exportInvestmentGroup(ObservableList<InvestmentGroup> list){
        Director director = Director.getInstance();
        Stage stage = director.getStage();
        FileChooser fc = new FileChooser();
        FileChooser.ExtensionFilter filter = new FileChooser.ExtensionFilter("Excel munkafüzet (*.xls)", "*.xls");
        fc.getExtensionFilters().add(filter);
        fc.setTitle("XLS exportálása...");
        fc.setInitialDirectory(File.listRoots()[0]);
        File exportXLS = fc.showSaveDialog(stage);
        try{
            WritableWorkbook wb = Workbook.createWorkbook(exportXLS);
            WritableSheet sheet = wb.createSheet("befektetes csoportok", 0);
            Label h1 = new Label(0,0,"Megnevezés");
            Label h2 = new Label(1,0,"Megjegyzés");
            Label h3 = new Label(2,0,"Hatály kezdete");
            Label h4 = new Label(3,0,"Hatály vége");
            sheet.addCell(h1);
            sheet.addCell(h2);
            sheet.addCell(h3);
            sheet.addCell(h4);
            for (int i=0; i< list.size(); i++)
            {
                Label name = new Label(0,i+1,list.get(i).getName());
                Label note = new Label(1,i+1,list.get(i).getNote());
                Label start = new Label(2,i+1,list.get(i).getStartDate());
                Label end = new Label(3,i+1,list.get(i).getEndDate());
                sheet.addCell(name);
                sheet.addCell(note);
                sheet.addCell(start);
                sheet.addCell(end);
            }
            wb.write();
            wb.close();
        }
        catch(Exception e)
        {
            System.err.println("Írási hiba \n" + e.getMessage());
        }
        return exportXLS;
    }
    
    // portfoliok exportalasa
    public static File exportPortfolio(ObservableList<Portfolio> list){
        Director director = Director.getInstance();
        Stage stage = director.getStage();
        FileChooser fc = new FileChooser();
        FileChooser.ExtensionFilter filter = new FileChooser.ExtensionFilter("Excel munkafüzet (*.xls)", "*.xls");
        fc.getExtensionFilters().add(filter);
        fc.setTitle("XLS exportálása...");
        fc.setInitialDirectory(File.listRoots()[0]);
        File exportXLS = fc.showSaveDialog(stage);
        try{
            WritableWorkbook wb = Workbook.createWorkbook(exportXLS);
            WritableSheet sheet = wb.createSheet("portfoliok", 0);
            Label h1 = new Label(0,0,"Megnevezés");
            Label h2 = new Label(1,0,"Hatály kezdete");
            Label h3 = new Label(2,0,"Hatály vége");
            sheet.addCell(h1);
            sheet.addCell(h2);
            sheet.addCell(h3);
            for (int i=0; i< list.size(); i++)
            {
                Label name = new Label(0,i+1,list.get(i).getName());
                Label start = new Label(1,i+1,list.get(i).getStartDate());
                Label end = new Label(2,i+1,list.get(i).getEndDate());
                sheet.addCell(name);
                sheet.addCell(start);
                sheet.addCell(end);
            }
            wb.write();
            wb.close();
        }
        catch(Exception e)
        {
            System.err.println("Írási hiba \n" + e.getMessage());
        }
        return exportXLS;
    }
    
    // ertekpapirok exportalasa
    public static File exportSecurities(ObservableList<Securities> list){
        Director director = Director.getInstance();
        Stage stage = director.getStage();
        FileChooser fc = new FileChooser();
        FileChooser.ExtensionFilter filter = new FileChooser.ExtensionFilter("Excel munkafüzet (*.xls)", "*.xls");
        fc.getExtensionFilters().add(filter);
        fc.setTitle("XLS exportálása...");
        fc.setInitialDirectory(File.listRoots()[0]);
        File exportXLS = fc.showSaveDialog(stage);
        try{
            WritableWorkbook wb = Workbook.createWorkbook(exportXLS);
            WritableSheet sheet = wb.createSheet("ertekpapirok", 0);
            Label h1 = new Label(0,0,"Azonosító");
            Label h2 = new Label(1,0,"Megnevezés");
            Label h3 = new Label(2,0,"Eszközcsoport");
            Label h4 = new Label(3,0,"Befektetési csoport");
            Label h5 = new Label(4,0,"Devizanem");
            Label h6 = new Label(5,0,"Alapcímlet");
            Label h7 = new Label(6,0,"71OJ");
            Label h8 = new Label(7,0,"71OJ_C");
            Label h9 = new Label(8,0,"71OK");
            Label h10 = new Label(9,0,"Lejárat");
            Label h11 = new Label(10,0,"Hatály kezdete");
            Label h12 = new Label(11,0,"Hatály vége");
            Label h13 = new Label(12,0,"Elszámolás alapja");
            sheet.addCell(h1);
            sheet.addCell(h2);
            sheet.addCell(h3);
            sheet.addCell(h4);
            sheet.addCell(h5);
            sheet.addCell(h6);
            sheet.addCell(h7);
            sheet.addCell(h8);
            sheet.addCell(h9);
            sheet.addCell(h10);
            sheet.addCell(h11);
            sheet.addCell(h12);
            sheet.addCell(h13);
            for (int i=0; i< list.size(); i++)
            {
                Label l1 = new Label(0,i+1,list.get(i).getISIN());
                Label l2 = new Label(1,i+1,list.get(i).getName());
                Label l3 = new Label(2,i+1,list.get(i).getInstrument());
                Label l4 = new Label(3,i+1,list.get(i).getInvestment());
                Label l5 = new Label(4,i+1,list.get(i).getCurrency());
                Label l6 = new Label(5,i+1,list.get(i).getDenomination());
                Label l7 = new Label(6,i+1,list.get(i).getTIOJ());
                Label l8 = new Label(7,i+1,list.get(i).getTIOJ_C());
                Label l9 = new Label(8,i+1,list.get(i).getTIOK());
                Label l10 = new Label(9,i+1,list.get(i).getExpiry());
                Label l11 = new Label(10,i+1,list.get(i).getStartDate());
                Label l12 = new Label(11,i+1,list.get(i).getEndDate());
                Label l13 = new Label(12,i+1,list.get(i).getAccountBase());
                sheet.addCell(l1);
                sheet.addCell(l2);
                sheet.addCell(l3);
                sheet.addCell(l4);
                sheet.addCell(l5);
                sheet.addCell(l6);
                sheet.addCell(l7);
                sheet.addCell(l8);
                sheet.addCell(l9);
                sheet.addCell(l10);
                sheet.addCell(l11);
                sheet.addCell(l12);
                sheet.addCell(l13);
            }
            wb.write();
            wb.close();
        }
        catch(Exception e)
        {
            System.err.println("Írási hiba \n" + e.getMessage());
        }
        return exportXLS;
    }
    
    // tranzakciotipusok exportalasa
    public static File exportTransaction(ObservableList<Transaction> list){
        Director director = Director.getInstance();
        Stage stage = director.getStage();
        FileChooser fc = new FileChooser();
        FileChooser.ExtensionFilter filter = new FileChooser.ExtensionFilter("Excel munkafüzet (*.xls)", "*.xls");
        fc.getExtensionFilters().add(filter);
        fc.setTitle("XLS exportálása...");
        fc.setInitialDirectory(File.listRoots()[0]);
        File exportXLS = fc.showSaveDialog(stage);
        try{
            WritableWorkbook wb = Workbook.createWorkbook(exportXLS);
            WritableSheet sheet = wb.createSheet("tranzakciok", 0);
            Label h1 = new Label(0,0,"Megnevezés");
            Label h2 = new Label(1,0,"Darabszám változással jár");
            Label h3 = new Label(2,0,"Beszerzési érték csökkentéssel jár");
            Label h4 = new Label(3,0,"Elhatárolt kamat");
            Label h5 = new Label(4,0,"Elhatárolt osztalék");
            Label h6 = new Label(5,0,"Piaci árfolyam nyereség");
            Label h7 = new Label(6,0,"Deviza árfolyam nyereség");
            Label h8 = new Label(7,0,"Hatály kezdete");
            Label h9 = new Label(8,0,"Hatály vége");
            sheet.addCell(h1);
            sheet.addCell(h2);
            sheet.addCell(h3);
            sheet.addCell(h4);
            sheet.addCell(h5);
            sheet.addCell(h6);
            sheet.addCell(h7);
            sheet.addCell(h8);
            sheet.addCell(h9);
            for (int i=0; i< list.size(); i++)
            {
                Label l1 = new Label(0,i+1,list.get(i).getName());
                Label l2 = new Label(1,i+1,list.get(i).getDeltaVolume());
                Label l3 = new Label(2,i+1,list.get(i).getDecrAV());
                Label l4 = new Label(3,i+1,list.get(i).getSepInterest());
                Label l5 = new Label(4,i+1,list.get(i).getSepDividend());
                Label l6 = new Label(5,i+1,list.get(i).getMktRateGain());
                Label l7 = new Label(6,i+1,list.get(i).getCurRateGain());
                Label l8 = new Label(7,i+1,list.get(i).getStartDate());
                Label l9 = new Label(8,i+1,list.get(i).getEndDate());
                sheet.addCell(l1);
                sheet.addCell(l2);
                sheet.addCell(l3);
                sheet.addCell(l4);
                sheet.addCell(l5);
                sheet.addCell(l6);
                sheet.addCell(l7);
                sheet.addCell(l8);
                sheet.addCell(l9);
            }
            wb.write();
            wb.close();
        }
        catch(Exception e)
        {
            System.err.println("Írási hiba \n" + e.getMessage());
        }
        return exportXLS;
    }
    
    // vagyonkezelok exportalasa
    public static File exportTrustee(ObservableList<Trustee> list){
        Director director = Director.getInstance();
        Stage stage = director.getStage();
        FileChooser fc = new FileChooser();
        FileChooser.ExtensionFilter filter = new FileChooser.ExtensionFilter("Excel munkafüzet (*.xls)", "*.xls");
        fc.getExtensionFilters().add(filter);
        fc.setTitle("XLS exportálása...");
        fc.setInitialDirectory(File.listRoots()[0]);
        File exportXLS = fc.showSaveDialog(stage);
        try{
            WritableWorkbook wb = Workbook.createWorkbook(exportXLS);
            WritableSheet sheet = wb.createSheet("ertekpapirok", 0);
            Label h1 = new Label(0,0,"Név");
            Label h2 = new Label(1,0,"Rövid név");
            Label h3 = new Label(2,0,"Irányítószám");
            Label h4 = new Label(3,0,"Település");
            Label h5 = new Label(4,0,"Cím");
            Label h6 = new Label(5,0,"Telefon");
            Label h7 = new Label(6,0,"Mobil");
            Label h8 = new Label(7,0,"Fax");
            Label h9 = new Label(8,0,"E-mail");
            Label h10 = new Label(9,0,"Kapcsolat");
            Label h11 = new Label(10,0,"Beosztás");
            Label h12 = new Label(11,0,"Hatály kezdete");
            Label h13 = new Label(12,0,"Hatály vége");
            sheet.addCell(h1);
            sheet.addCell(h2);
            sheet.addCell(h3);
            sheet.addCell(h4);
            sheet.addCell(h5);
            sheet.addCell(h6);
            sheet.addCell(h7);
            sheet.addCell(h8);
            sheet.addCell(h9);
            sheet.addCell(h10);
            sheet.addCell(h11);
            sheet.addCell(h12);
            sheet.addCell(h13);
            for (int i=0; i< list.size(); i++)
            {
                Label l1 = new Label(0,i+1,list.get(i).getName());
                Label l2 = new Label(1,i+1,list.get(i).getShortName());
                Label l3 = new Label(2,i+1,list.get(i).getZip());
                Label l4 = new Label(3,i+1,list.get(i).getCity());
                Label l5 = new Label(4,i+1,list.get(i).getAddress());
                Label l6 = new Label(5,i+1,list.get(i).getTel());
                Label l7 = new Label(6,i+1,list.get(i).getMobile());
                Label l8 = new Label(7,i+1,list.get(i).getFax());
                Label l9 = new Label(8,i+1,list.get(i).getMail());
                Label l10 = new Label(9,i+1,list.get(i).getConnection());
                Label l11 = new Label(10,i+1,list.get(i).getAssignment());
                Label l12 = new Label(11,i+1,list.get(i).getStartDate());
                Label l13 = new Label(12,i+1,list.get(i).getEndDate());
                sheet.addCell(l1);
                sheet.addCell(l2);
                sheet.addCell(l3);
                sheet.addCell(l4);
                sheet.addCell(l5);
                sheet.addCell(l6);
                sheet.addCell(l7);
                sheet.addCell(l8);
                sheet.addCell(l9);
                sheet.addCell(l10);
                sheet.addCell(l11);
                sheet.addCell(l12);
                sheet.addCell(l13);
            }
            wb.write();
            wb.close();
        }
        catch(Exception e)
        {
            System.err.println("Írási hiba \n" + e.getMessage());
        }
        return exportXLS;
    }
    
    // leltari adatok exportalasa
    public static File exportOperation(ObservableList<Inventory> list){
        Director director = Director.getInstance();
        Stage stage = director.getStage();
        FileChooser fc = new FileChooser();
        FileChooser.ExtensionFilter filter = new FileChooser.ExtensionFilter("Excel munkafüzet (*.xls)", "*.xls");
        fc.getExtensionFilters().add(filter);
        fc.setTitle("XLS exportálása...");
        fc.setInitialDirectory(File.listRoots()[0]);
        File exportXLS = fc.showSaveDialog(stage);
        try{
            WritableWorkbook wb = Workbook.createWorkbook(exportXLS);
            WritableSheet sheet = wb.createSheet("ertekpapirok", 0);
            Label h1 = new Label(0,0,"Vételi ID");
            Label h2 = new Label(1,0,"Beszerzés dátuma");
            Label h3 = new Label(2,0,"Mennyiség");
            Label h4 = new Label(3,0,"Névérték");
            Label h5 = new Label(4,0,"Beszerzési érték");
            Label h6 = new Label(5,0,"Piaci érték");
            Label h7 = new Label(6,0,"ÉK - kamat");
            Label h8 = new Label(7,0,"ÉK - osztalék");
            Label h9 = new Label(8,0,"ÉK - egyéb piaci");
            Label h10 = new Label(9,0,"ÉK - deviza árfolyam-különbözet");
            Label h11 = new Label(10,0,"Ügyletszám");
            Label h12 = new Label(11,0,"Elszámolt mennyiség/névérték");
            Label h13 = new Label(12,0,"Könyv szerinti érték");
            Label h14 = new Label(13,0,"Vételárban elszámolt kamat");
            Label h15 = new Label(14,0,"Realizált kamat");
            Label h16 = new Label(15,0,"Realizált osztalék");
            Label h17 = new Label(16,0,"Realizált egyéb piaci");
            Label h18 = new Label(17,0,"Realizált deviza árfolyam-különbözet");
            Label h19 = new Label(18,0,"Kivezetett kamat");
            Label h20 = new Label(19,0,"Kivezetett osztalék");
            Label h21 = new Label(20,0,"Kivezetett egyéb piaci");
            Label h22 = new Label(21,0,"Kivezetett deviza árfolyam-különbözet");
            sheet.addCell(h1);
            sheet.addCell(h2);
            sheet.addCell(h3);
            sheet.addCell(h4);
            sheet.addCell(h5);
            sheet.addCell(h6);
            sheet.addCell(h7);
            sheet.addCell(h8);
            sheet.addCell(h9);
            sheet.addCell(h10);
            sheet.addCell(h11);
            sheet.addCell(h12);
            sheet.addCell(h13);
            sheet.addCell(h14);
            sheet.addCell(h15);
            sheet.addCell(h16);
            sheet.addCell(h17);
            sheet.addCell(h18);
            sheet.addCell(h19);
            sheet.addCell(h20);
            sheet.addCell(h21);
            sheet.addCell(h22);
            for (int i=0; i< list.size(); i++)
            {
                Label l1 = new Label(0,i+1,list.get(i).getPurchaseId());
                Label l2 = new Label(1,i+1,list.get(i).getAcquisitionDate());
                jxl.write.Number l3 = new jxl.write.Number(2,i+1,list.get(i).getQuantity());
                jxl.write.Number l4 = new jxl.write.Number(3,i+1,list.get(i).getNominal());
                jxl.write.Number l5 = new jxl.write.Number(4,i+1,list.get(i).getAcquisitionValue());
                jxl.write.Number l6 = new jxl.write.Number(5,i+1,list.get(i).getMarketValue());
                jxl.write.Number l7 = new jxl.write.Number(6,i+1,list.get(i).getDerecogniseInterest());
                jxl.write.Number l8 = new jxl.write.Number(7,i+1,list.get(i).getDerecogniseDividend());
                jxl.write.Number l9 = new jxl.write.Number(8,i+1,list.get(i).getDerecogniseRateDelta());
                jxl.write.Number l10 = new jxl.write.Number(9,i+1,list.get(i).getDerecogniseCurrencyRateDelta());
                Label l11 = new Label(10,i+1,list.get(i).getSerial());
                jxl.write.Number l12 = new jxl.write.Number(11,i+1,list.get(i).getAccountedBase());
                jxl.write.Number l13 = new jxl.write.Number(12,i+1,list.get(i).getAccountedValue());
                jxl.write.Number l14 = new jxl.write.Number(13,i+1,list.get(i).getAccumulatedInterestAccountedPrice());
                jxl.write.Number l15 = new jxl.write.Number(14,i+1,list.get(i).getRealisedInterest());
                jxl.write.Number l16 = new jxl.write.Number(15,i+1,list.get(i).getRealisedDividend());
                jxl.write.Number l17 = new jxl.write.Number(16,i+1,list.get(i).getRealisedRateDelta());
                jxl.write.Number l18 = new jxl.write.Number(17,i+1,list.get(i).getRealisedCurrencyRateDelta());
                jxl.write.Number l19 = new jxl.write.Number(18,i+1,list.get(i).getDerecognisedInterest());
                jxl.write.Number l20 = new jxl.write.Number(19,i+1,list.get(i).getDerecogniseDividend());
                jxl.write.Number l21 = new jxl.write.Number(20,i+1,list.get(i).getDerecognisedRateDelta());
                jxl.write.Number l22 = new jxl.write.Number(21,i+1,list.get(i).getDerecognisedCurrencyRateDelta());
                sheet.addCell(l1);
                sheet.addCell(l2);
                sheet.addCell(l3);
                sheet.addCell(l4);
                sheet.addCell(l5);
                sheet.addCell(l6);
                sheet.addCell(l7);
                sheet.addCell(l8);
                sheet.addCell(l9);
                sheet.addCell(l10);
                sheet.addCell(l11);
                sheet.addCell(l12);
                sheet.addCell(l13);
                sheet.addCell(l14);
                sheet.addCell(l15);
                sheet.addCell(l16);
                sheet.addCell(l17);
                sheet.addCell(l18);
                sheet.addCell(l19);
                sheet.addCell(l20);
                sheet.addCell(l21);
                sheet.addCell(l22);
            }
            wb.write();
            wb.close();
        }
        catch(Exception e)
        {
            System.err.println("Írási hiba \n" + e.getMessage());
        }
        return exportXLS;
    }
    
    // nyitoleltar betoltese
    public static void importInventory()
    {
        Director director = Director.getInstance();
        Stage stage = director.getStage();
        AccessInfo ai = director.getAccessInfo();
        FileChooser fc = new FileChooser();
        FileChooser.ExtensionFilter filter = new FileChooser.ExtensionFilter("Excel munkafüzet (*.xls)", "*.xls");
        fc.getExtensionFilters().add(filter);
        fc.setTitle("Nyitóleltár importálása...");
        fc.setInitialDirectory(File.listRoots()[0]);
        File leltar = fc.showOpenDialog(stage);
        
        try{
            Workbook workbook = Workbook.getWorkbook(leltar);
            // nyitoleltar munkalap betoltese
            Sheet sheet = workbook.getSheet("nyitoleltar");
            int rowCount = sheet.getRows();
            Connection con = DriverManager.getConnection("jdbc:sqlserver://" + ai.getIPAddress() + ":" + ai.getPort() + ";databaseName=" + ai.getDBName(), ai.getUser(), ai.getPass());
            // tranzakcios adatok torlese
            PreparedStatement deleteLeltar = con.prepareStatement("DELETE FROM leltar");
            deleteLeltar.execute();
            PreparedStatement deleteLinks = con.prepareStatement("DELETE FROM muv_vk");
            deleteLinks.execute();
            PreparedStatement deleteTran = con.prepareStatement("DELETE FROM muvelet");
            deleteTran.execute();
            // ISIN tabla azonosito
            int isin_id;
            // PORTFOLIO tabla azonosito
            int port_id;
            // VAGYONKEZELO.vk_id azonosito
            int vk_id;
            // 2. sortol kezdve minden sorra
            for (int i = 1; i < rowCount; i++)
            {
                // sor betoltese
                Cell[] bef = sheet.getRow(i);
                try{
                    // isin id meghatarozasa
                    PreparedStatement isinQuery = con.prepareStatement("SELECT id FROM isin WHERE ISIN = ? AND ? BETWEEN hatalytol AND hatalyig");
                    isinQuery.setNString(1,bef[2].getContents());
                    isinQuery.setDate(2, new Date(new java.util.Date().getTime()));
                    ResultSet isinResult = isinQuery.executeQuery();
                    isinResult.next();
                    isin_id=isinResult.getInt(1);
                    
                    // portfolio id meghatarozasa
                    PreparedStatement portQuery = con.prepareStatement("SELECT id FROM portfolio WHERE megnev = ? AND ? BETWEEN hatalytol AND hatalyig");
                    portQuery.setNString(1,bef[3].getContents());
                    portQuery.setDate(2, new Date(new java.util.Date().getTime()));
                    ResultSet portResult = portQuery.executeQuery();
                    portResult.next();
                    port_id=portResult.getInt(1);
                    
                    // id_vk megatarozasa
                    PreparedStatement vkQuery = con.prepareStatement("SELECT id_vk FROM vagyonkezelo WHERE r_nev = ? AND ? BETWEEN hatalytol AND hatalyig");
                    vkQuery.setNString(1,bef[1].getContents());
                    vkQuery.setDate(2, new Date(new java.util.Date().getTime()));
                    ResultSet vkResult = vkQuery.executeQuery();
                    vkResult.next();
                    vk_id=vkResult.getInt(1);
                    
                    // adatok betoltese
                    String vetel_id = bef[0].getContents();
                    java.util.Date b_dat = ((DateCell)bef[4]).getDate();
                    int mennyiseg = (int)((NumberCell)bef[5]).getValue();
                    double nevertek = ((NumberCell)bef[6]).getValue();
                    long b_ertek = (long)((NumberCell)bef[7]).getValue();
                    long p_ertek = (long)((NumberCell)bef[8]).getValue();
                    long ek_kamat = (long)((NumberCell)bef[9]).getValue();
                    long ek_osztalek = (long)((NumberCell)bef[10]).getValue();
                    long ek_egy = (long)((NumberCell)bef[11]).getValue();
                    long ek_dev = (long)((NumberCell)bef[12]).getValue();
                    long barf_p = (long)((NumberCell)bef[13]).getValue();
                    float barf_d = (float)((NumberCell)bef[14]).getValue();
                    
                    // leltar tabla modositasa
                    PreparedStatement insertAction = con.prepareStatement("INSERT INTO leltar VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                    insertAction.setNString(1, vetel_id);
                    insertAction.setInt(2, vk_id);
                    insertAction.setInt(3, isin_id);
                    insertAction.setInt(4,port_id);
                    insertAction.setDate(5, new Date(b_dat.getTime()));
                    insertAction.setInt(6,mennyiseg);
                    insertAction.setDouble(7, nevertek);
                    insertAction.setLong(8, b_ertek);
                    insertAction.setLong(9, p_ertek);
                    insertAction.setLong(10, ek_kamat);
                    insertAction.setLong(11, ek_osztalek);
                    insertAction.setLong(12, ek_egy);
                    insertAction.setLong(13, ek_dev);
                    insertAction.setLong(14, barf_p);
                    insertAction.setFloat(15, barf_d);
                    insertAction.setNString(16, "X"+vetel_id);
                    insertAction.executeUpdate();
                }
                catch(SQLException sqle)
                {
                    System.err.println(sqle.getMessage());
                }
            }
            con.close();
        }
        catch (Exception e)
        {
            System.err.println("Hiba a nyitóleltár betöltése közben: " + e.getMessage());
            e.printStackTrace();
        }
    }
}
